﻿Imports QL_GioiThieu_NhaThue.sqlDataProvider
Imports System.Data.SqlClient


Public Class ThamSoDAO
    Public Shared Function GetAll() As List(Of ThamSoDTO)
        Dim listThamSo As New List(Of ThamSoDTO)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_SelectTHAMSOsAll"
            cmd.Connection = cn
            cn.Open()

            Dim rd As SqlDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim thamso As ThamSoDTO = New ThamSoDTO()
                thamso.iSLNhaToiDaMoiQuan = rd.GetInt32(0)
                thamso.iSLLoaiNha = rd.GetInt32(1)
                thamso.iSoluongQuan = rd.GetInt32(2)

                listThamSo.Add(thamso)
            End While
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return listThamSo
    End Function

    Public Shared Function SoLuongLoaiNha() As Integer
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Dim cmd As New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "demSL_LoaiNha"
        cmd.Connection = cn

        cn.Open()
        Dim sl As Integer = cmd.ExecuteScalar()
        cn.Close()

        Return sl
    End Function
    
    Public Shared Sub Update1(ByVal para As SqlParameter, ByVal para1 As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateSLQuan_NhaTD"
            cmd.Connection = cn

            cmd.Parameters.Add(para)
            cmd.Parameters.Add(para1)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
    End Sub
    Public Shared Sub Update(ByVal sqlParameters As SqlParameter)
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_UpdateThamSo"
            cmd.Connection = cn

            cmd.Parameters.Add(sqlParameters)
            cn.Open()
            cmd.ExecuteNonQuery()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
       
    End Sub
  

    Public Shared Function LaySLNhaToiDa() As Integer
        Dim iSLNhaToiDa As Integer
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "usp_LaySLNhaToiDa"
            cmd.Connection = cn

            cn.Open()
            iSLNhaToiDa = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return iSLNhaToiDa
    End Function


    Public Shared Function LaySLQuan_SLNhaTD() As ThamSoDTO
        Dim ThamSo As New ThamSoDTO()
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SLQuan_NhaTD"
            cmd.Connection = cn

            cn.Open()
            Dim rd As SqlDataReader = cmd.ExecuteReader()
            If (rd.Read()) Then
                ThamSo.iSLNhaToiDaMoiQuan = rd.GetInt32(0)
                ThamSo.iSoluongQuan = rd.GetInt32(1)
            End If
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try
        
        Return ThamSo
    End Function

    Public Shared Function LaySLQuanTD() As Integer
        Dim iSLQuanTD As Integer
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SL_QuanToiDa"
            cmd.Connection = cn

            cn.Open()
            iSLQuanTD = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return iSLQuanTD
    End Function
    Public Shared Function LaySLNhaTD() As Integer
        Dim iSLNhaToiDa As Integer
        Dim cn As SqlConnection = New SqlConnection(_strConnectionString)
        Try
            Dim cmd As New SqlCommand()
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "SL_NhaToiDa"
            cmd.Connection = cn

            cn.Open()
            iSLNhaToiDa = cmd.ExecuteScalar()
            cn.Close()
        Catch ex As Exception
            MessageBox.Show("Phát hiện lỗi!" + Environment.NewLine + ex.Message, "THÔNG BÁO", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

        Return iSLNhaToiDa
    End Function

End Class
